package com.hxzy;

import entity.BookType;
import entity.Books;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Dbutils1 {
    final  String DRIVER="com.mysql.cj.jdbc.Driver";
    final String URL="jdbc:mysql://localhost:3306/studentdb";
    final  String USER="root";
    final String PWD="123";

    @Test
    public void  insert(){
        Connection connection=null;
        DbUtils.loadDriver(DRIVER);
        Books books = new Books();
        books.setName("莽荒纪");
        books.setAuthor("我吃西红柿");
        books.setTime(new Date());
        BookType bookType = new BookType();
        bookType.setId(4);
        books.setBookType(bookType
        );
        try {
            connection = DriverManager.getConnection(URL, USER, PWD);
            QueryRunner queryRunner=new QueryRunner();
            String sql="insert into books (b_name,b_author,b_time,b_type) values (?,?,?,?)";
            Object[] objects={
                    books.getName(),
                    books.getAuthor(),
                    books.getTime(),
                    books.getBookType().getId()
            };
            int update = queryRunner.update(connection, sql, objects);
            System.out.println(update==0?"新增sql成功":"失败了哟");

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }
    @Test
    public void  findId(){
        DbUtils.loadDriver(DRIVER);
        Connection connection = null;
        try {
            connection=DriverManager.getConnection(URL,USER,PWD);
            String sql = "select b_id `id`,b_name `name`,b_author `author`,b_time `time`,b_type from books where b_id = ?";
            int ids=2;

            QueryRunner queryRunner=new QueryRunner();
            ResultSetHandler<Books> handler=(resultSet)->{
                if(resultSet.next()){
                    int id = resultSet.getInt(1);
                    String name = resultSet.getString(2);
                    String author = resultSet.getString(3);
                    java.sql.Date date = resultSet.getDate(4);
                    Date date1=date;
                    int type = resultSet.getInt(5);
                    BookType bookType=new BookType();
                    bookType.setId(type);
                    Books books=new Books(id,name,author,date1,bookType);
                    return books;

                }
                return null;

            };
            Books query = queryRunner.query(connection, sql, handler,ids);
            System.out.println(query);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }


    }
    @Test
    public void findAll(){
        DbUtils.loadDriver(DRIVER);
        Connection connection=null;
        try {
            connection = DriverManager.getConnection(URL, USER, PWD);
            String sql = "select b_id `id`,b_name `name`,b_author `author`,b_time `time`,b_type from books";
            QueryRunner queryRunner=new QueryRunner();
            ResultSetHandler<List<Books> > books=(resultSet)->{
                List<Books> books1=new ArrayList<>();
                while (resultSet.next()){
                    int anInt = resultSet.getInt(1);
                    String string = resultSet.getString(2);
                    String string1 = resultSet.getString(3);
                    java.sql.Date date = resultSet.getDate(4);
                    int anInt1 = resultSet.getInt(5);
                    BookType bookType=new BookType();
                    bookType.setId(anInt1);
                    Books books2=new Books(anInt,string,string1,date,bookType);
                   books1.add(books2);
                   return books1;
                }
                return null;};

            List<Books> query = queryRunner.query(connection, sql, books);
            query.forEach(System.out::println);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }


    @Test
    public void  updateSql(){
        DbUtils.loadDriver(DRIVER);
        Connection connection=null;
        Books books=new Books();
        String name ="斗罗大陆";
        String author="天蚕土豆";
        books.setName(name);
        books.setAuthor(author);
        int id=14;
        books.setId(id);
        try {
            connection = DriverManager.getConnection(URL, USER, PWD);

            String sql="UPDATE books SET b_name = ?,b_author = ? WHERE b_id = ?";


            QueryRunner queryRunner=new QueryRunner();
            Object[] objects= {

                    books.getName(),
                    books.getAuthor(),
                    books.getId()


            };

            int update = queryRunner.update(connection, sql,objects);
            System.out.println(update==0?"失败":"成功");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}
